﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Hisun.Models;
using System.Data.OracleClient;
using System.Collections.Generic;

namespace Hisun.Controllers
{
    /// <summary>
    /// AbcSync为同步财务Oracle数据库的类
    /// 调用方法为new AbcSync("会计期间")
    /// 
    /// </summary>
    public class AbcSync
    {
        const string connstring = "Data Source=hisun;Persist Security Info=True;User ID=fams_3304;Password=fams_3304;Unicode=True";
        OracleConnection conn = new OracleConnection(connstring);
        HisunDBDataContext db = new HisunDBDataContext();
        private string kjqj;
        public string Kjqj
        {
            set { kjqj = value; }
        }
        public AbcSync(string kjqj1)
        {
            kjqj = kjqj1;
            try
            {
                conn.Open();
            }
            catch
            {
                throw new Exception("无法打开财务Oracle数据库");
            }

        }
      
        /// <summary>
        /// 读取财务Oracle数据库中的半成品领料
        /// </summary>
        /// <returns>DataTable Oracle查询结果</returns>
//        private DataTable GetMeterical_PIP()
//        {
//            DataTable dt = new DataTable();
////            string sql = @"-- union  all
//// select  d.
////k.vcaccount_no 帐户编号,
////  k.vcaccount_name as 帐户名称,
//// cl.vcMaterialNo 材料编号,
//// cl.vcmatname_spec as 材料名称 ,
//// d.nmaterial_cost 金额,
//// d.namount 数量,
//// cl.vcmatunit as 单位,  
////(case when  k.vcaccount_no  like '215%' then  445 else null  end ) as 作业中心ID ,0,
////'半成品领料' 
////from tma_materialinout_m m,tma_materialinout_d d,tge_inner_account k,tge_inner_account tk,vw_material cl,tge_integrateoption ti,vw_person ry,vw_storage ck 
////where m.nid = d.nid 
////and k.nid(+) = m.naccount_id 
////AND m.ndept_id = tk.nid 
////and m.nrecord_type = ti.nhxh(+) 
////and cl.nmaterialid = d.ngoods_id 
////and m.nbill_type = 8 and m.ncreate_mark = 0 
////and m.ncreater_id = ry.personid(+) and d.narea_id = ck.nStorageID(+) 
////and m.ncompany_id =  4  
////and m.drecord_date = :KJQJ  -- to_date('2009-01-01','YYYY-MM-DD') ";
//            try
//            {
//                OracleCommand cmd = new OracleCommand(sql, conn);
//                cmd.Parameters.Add("KJQJ", OracleType.DateTime).Value = getkjqj();
//                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
//                adapter.Fill(dt);
//            }
//            catch
//            {
//                throw new Exception("读取财务Oracle数据库失败");
//            }
//            return dt;


//        }


        #region 导入产品领料、材料手工录入(质检中心领料)、材料调整,三块数据放到数据集中。
        /// <summary>
        /// 同步材料领用表---------2009年3月3日 杨莹增加
        /// 经过调研，发现产品领料的数据需要从另外的数据源导入
        /// 因此将产品领料方法单独编写代码。
        /// </summary>
        /// <returns>是否成功</returns>
        private DataTable GetMeterial_P()
        {

//2010年8月1日，改代码。
//            select t.nid as NID,
// ina2.vcaccount_no 帐户编号
//,ina2.vcaccount_name 帐户名称
//,cp.vcbm 材料编码,
//cp.vctym 材料名称,
//nvl(t.nin_cost,0) 金额,
//nvl(t.nin_amount,0) 数量,
//cp.vcjldw 计量单位 ,
//null as 作业中心ID ,
//1 as 产品领料  ,
//'财务手工录入' as 来源标识
//from tca_prodbomaccount t ,tca_prodbom bom,tbcpb cp, vw_inner_account ina2 
// where t.nyear= to_char(:KJQJ   ,'YYYY') 
// and t.nmonth= to_char(:KJQJ   ,'mm') 
// and t.nin_amount<>0
// and t.nbom_id=bom.nid
// and bom.ncost_item_id='22'
// and bom.nitem_sequence='21'
// and cp.nhxh(+)=bom.nmaterial_id
// and bom.nprod_id=ina2.nhxh


            DataTable dt = new DataTable();
            string sql = @"
select m.nid ,
 ia.vcaccount_no 帐户编号
,ia.vcaccount_name 帐户名称
,cp.vcprodno 材料编码,
cp.vcprodname 材料名称,
nvl(d.nmoney,0) 金额,
nvl(d.namount,0) 数量,
cp.vcprodunit 计量单位 ,
null as 作业中心ID ,
1 as 产品领料  ,
'财务手工录入' as 来源标识
from  tca_prodcost_m m,tca_prodcost_d d,tge_inner_account ia,vw_product cp
 where m.nid =d.tca_nid 
 and m.nprod_id = ia.nid 
 and ia.naccount_mark = 2 
 and d.nmaterial_id = cp.nprodid 
 and m.nbill_type = 3 
 and m.drecord_date = :KJQJ
--to_date('2009-01-01','yyyy-mm-dd')  
--order by ia.vcaccount_no
 
--and m.nstorage_id =  61  
 
 union all
 select
 d.nid,
 k.vcaccount_no 帐户编号,k.vcaccount_name as 帐户名称,
(case when m.ncost_item_id = '21' then cl.vcbm  else cp.vcbm end) 材料或产品编号,
(case when m.ncost_item_id = '21' then cl.vcmc || cl.vcgg else cp.vctym||cp.vcgg end) as 材料或产品名称,
nvl(d.nmoney,0) 金额,
nvl(d.namount,0) 数量,
(case when m.ncost_item_id = '21' then cl.vcjldw else cp.vcjldw end) as 单位,
(case when  k.vcaccount_no  like '215%' then  445 else null  end ) as 作业中心ID,
(case when m.ncost_item_id = '21' then 0 else 1 end)  as 产品领料,
'技术中心'  
from tge_inner_account k,tbclb cl,tbcpb cp,tca_prodcost_d d,tca_prodcost_m m 
where m.nid = d.tca_nid 
and d.nmaterial_id = cl.nhxh(+) 
and d.nmaterial_id = cp.nhxh(+) 
and m.nprod_id = k.nid(+) and m.nbill_type = 1 
and m.nimport_mark = 0 
and m.ncost_item_id in (21,22) 
and m.drecord_date = :KJQJ --to_date('2009-01-01','yyyy-mm-dd') 
 
 union  all
 select  d.ndetail_id,
k.vcaccount_no 帐户编号,
  k.vcaccount_name as 帐户名称,
 cl.vcMaterialNo 材料编号,
 cl.vcmatname_spec as 材料名称 ,
 nvl(d.nmaterial_cost,0) 金额,
 nvl(d.namount,0) 数量,
 cl.vcmatunit as 单位,  
(case when  k.vcaccount_no  like '215%' then  445 else null  end ) as 作业中心ID ,0,
'半成品领料' 
from tma_materialinout_m m,tma_materialinout_d d,tge_inner_account k,tge_inner_account tk,vw_material cl,tge_integrateoption ti,vw_person ry,vw_storage ck 
where m.nid = d.nid 
and k.nid(+) = m.naccount_id 
AND m.ndept_id = tk.nid 
and m.nrecord_type = ti.nhxh(+) 
and cl.nmaterialid = d.ngoods_id 
and m.nbill_type = 8 and m.ncreate_mark = 0 
and m.ncreater_id = ry.personid(+) and d.narea_id = ck.nStorageID(+) 
and m.ncompany_id =  4  
and m.drecord_date = :KJQJ  -- to_date('2009-01-01','YYYY-MM-DD')
 
";
//            string sql = @"select ina2.vcaccount_no 产品ID
//,ina2.vcaccount_name  产品名称
//,t.nin_amount 数量
//,t.nin_cost 金额
//,cp.vcbm 材料编号
//,cp.vcjldw 单位
//,cp.vctym 材料名称 
//from tca_prodbomaccount t ,tca_prodbom bom,tbcpb cp, vw_inner_account ina2 
// where t.nyear='2009'
// and t.nmonth='01'
// and t.nin_amount<>0
// and t.nbom_id=bom.nid
// and bom.ncost_item_id='22'
// and bom.nitem_sequence='21'
// and cp.nhxh(+)=bom.nmaterial_id
// and bom.nprod_id=ina2.nhxh";

            try
            {
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.Parameters.Add("KJQJ", OracleType.DateTime).Value = getkjqj();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(dt);
            }
            catch
            {
                throw new Exception("读取财务Oracle数据库失败");
            }
            return dt;

        }
        #endregion



        #region 产品领料的代码，将数据填充到TABC_CW_Meterial_P
        public bool Meterial_P_Sync()
        {
            DataTable tb = GetMeterial_P();



            db.ExecuteCommand("update   TABC_CW_Meterial_P  set Del=1 ");
           // db.SubmitChanges();
    
            List<TABC_CW_Meterial_P> row2 = new List<TABC_CW_Meterial_P>();
            foreach (DataRow row in tb.Rows)
            {
                var q = from a in db.TABC_CW_Meterial_P
                        where a.DetailID==Convert.ToInt64(row["NID"])
                        && a.来源标识== row["来源标识"].ToString()
                            select a ;
 
                if (q.Count() > 0)
                {
                    var edit = q.First();
                    edit.Del = 0;
                    edit.金额 = Convert.ToDecimal(row["金额"]);
                    edit.数量 = Convert.ToDecimal(row["数量"]);
                   
                }
                else  
                { 

                    TABC_CW_Meterial_P dsrow = new TABC_CW_Meterial_P();
                    dsrow.DetailID = Convert.ToInt64(row["NID"]);
                    dsrow.产品ID = row["帐户编号"].ToString();
                    dsrow.产品名称 = row["帐户名称"].ToString();
                    dsrow.材料编码 = row["材料编码"].ToString();
                    dsrow.材料名称 = row["材料名称"].ToString();
                    dsrow.金额 = Decimal.Parse(row["金额"].ToString());
                    dsrow.数量 = Decimal.Parse(row["数量"].ToString());
                    dsrow.计量单位 = row["计量单位"].ToString();
                    dsrow.来源标识 = row["来源标识"].ToString();
                    if (row["作业中心ID"].ToString() != "")
                        dsrow.作业中心ID = Convert.ToInt32(row["作业中心ID"].ToString());
                    if (row["产品领料"].ToString() == "1")
                    {
                        dsrow.产品领料 = true;
                    }
                    else
                    {
                        dsrow.产品领料 = false;
                    }
                    dsrow.Del = 0;
                    row2.Add(dsrow);
                    
                }



                


            }
            foreach (TABC_CW_Meterial_P dr in row2)
            {
                db.TABC_CW_Meterial_P.InsertOnSubmit(dr);
               

            }
            try
            {
                db.SubmitChanges();
                db.ExecuteCommand("delete from TABC_CW_Meterial_P where Del=1");
            }
            catch
            {
                return false;
            }
           
            return true;

        }

        #endregion




        #region 材料领用代码，将数据填充到TABC_CW_Meterial
        public bool Meterial_Sync()
        {
            DataTable tb = getcailiao();
            DateTime dt_ctsj=DateTime.MinValue;
            //try
            ////{
            //var q = from a in db.TABC_CW_Meterial select a;
            //db.TABC_CW_Meterial.DeleteAllOnSubmit(q);
            db.ExecuteCommand("delete from TABC_CW_Meterial ");
            //db.SubmitChanges();
           // q = null;
            List<TABC_CW_Meterial> row2 = new List<TABC_CW_Meterial>();
            foreach (DataRow row in tb.Rows)
            {
                TABC_CW_Meterial dsrow = new TABC_CW_Meterial();
                dsrow.NID = long.Parse(row["NID"].ToString());
                dsrow.领用人 = row["领用人"].ToString();
                dsrow.部门 = row["部门"].ToString();
                dsrow.产品ID = row["产品ID"].ToString();
                dsrow.产品名称 = row["产品名称"].ToString();
                DateTime.TryParse(row["出库日期"].ToString(),out dt_ctsj);
                dsrow.出库日期 = dt_ctsj;
                dsrow.材料编码 = row["材料编码"].ToString();
                dsrow.材料名称 = row["材料名称"].ToString();
                dsrow.计量单位 = row["计量单位"].ToString();
                dsrow.领料单号 = row["领料单号"].ToString();
                dsrow.金额 = Decimal.Parse(row["金额"].ToString());
              //  dsrow.单价 = Decimal.Parse(row["单价"].ToString());
                dsrow.数量 = Decimal.Parse(row["数量"].ToString());
              //  dsrow.会计科目 = row["会计科目"].ToString();
                ////if (row["产品领料"].ToString() == "1")
                ////{
                ////    dsrow.产品领料 = true;
                ////}
                ////else
                ////{
                   dsrow.产品领料 = false;
                ////}

                row2.Add(dsrow);
            }
            foreach (TABC_CW_Meterial dr in row2)
            {
                db.TABC_CW_Meterial.InsertOnSubmit(dr);

            }
        //    try
       //     {
                db.SubmitChanges();
       //     }
            //catch
            //{
            //    return false;
            //}
            //try
            //{
            //   // db.产品原材料导入(kjqj);

            //}
            //catch
            //{
            //    return false;
            //}
            return true;

        }
      #endregion


        /// <summary>
        /// 产品帐户表同步
        /// </summary>
        /// <returns>是否成功</returns>
        public bool Product_JS_Sync()
        {
            DataTable dt = GetAccount_Info();
           /* var q = (from a in db.TABC_JS_Product
                     select new
                     {
                         a.产品账户ID
                     }).ToArray();*/
         //   int i = 0;
            List<TABC_JS_Product> update_rows = new List<TABC_JS_Product>();
            //bool isadd = false;
            foreach (DataRow row in dt.Rows)
            {
              //  isadd = true;
                if (row[0].ToString().Length == 6)
                {
                    var q = from a in db.TABC_JS_Product where a.产品账户ID==row[0].ToString() select a;
                    if (q.Count() > 0)
                    {
                        var u = q.Single();
                        u.产品账户名称 = row["产品账户名称"].ToString();
                        u.产品ID = row["产品ID"].ToString();
                        u.产品辅码 = row["产品辅码"].ToString();
                        u.产品名称 = row["产品名称"].ToString();
                        u.产品助记码 = row["产品助记码"].ToString();
                        u.规格型号 = row["规格型号"].ToString();
                        u.计量单位 = row["计量单位"].ToString();
                        u.车间 = row["车间"].ToString();
                        if (row["停用"].ToString() == "1")
                        {
                            u.停用 = true;
                        }
                        else
                        {
                            u.停用 = false;
                        }

                        db.SubmitChanges();

                    }
                    else
                    {

                        TABC_JS_Product u = new TABC_JS_Product();
                        u.公司ID = "hisun";
                        u.产品账户ID = row["产品账户ID"].ToString();
                        u.产品账户名称 = row["产品账户名称"].ToString();
                        u.产品ID = row["产品ID"].ToString();
                        u.产品辅码 = row["产品辅码"].ToString();
                        u.产品名称 = row["产品名称"].ToString();
                        u.产品助记码 = row["产品助记码"].ToString();
                        u.规格型号 = row["规格型号"].ToString();
                        u.计量单位 = row["计量单位"].ToString();
                        u.车间 = row["车间"].ToString();
                        if (row["停用"].ToString() == "1")
                        {
                            u.停用 = true;
                        }
                        else
                        {
                            u.停用 = false;
                        }
                        update_rows.Add(u);
                    
                    
                    }
                     


                    //for (i = 0; i < q.Count(); i++) //循环判断是否存在该帐户ID，如果存在，这不添加;
                    //{
                    //    if (q[i].产品账户ID == row[0].ToString())
                    //    {
                    //        isadd = false;
                    //    }
                    //} 
                }
            }
            foreach (TABC_JS_Product updaterow in update_rows)
            {
                db.TABC_JS_Product.InsertOnSubmit(updaterow);

            } 
            try
            {
                db.SubmitChanges();
            }
            catch
            {
                //new Exception("写入数据失败");
                return false;
            }
            return true;


        }
        /// <summary>
        /// 同步原材料计划价格表
        /// </summary>
        /// <returns></returns>
        public bool PlanPrice_Sync()
        {
            List<string[]> PlanPriceS = Get_PlanPrice();
            string sql=string.Empty;
            foreach (string[] PlanPrice in PlanPriceS)
            {
               var q  = from a in db.TABC_JS_Meterial_PlanPrice 
                        where a.材料ID == PlanPrice[0] &&
                        a.年==PlanPrice[1] select a;
               if (q.Count() < 1)
               {
                   sql = string.Format(@"INSERT INTO  [TABC_JS_Meterial_PlanPrice]
                      ([材料ID]
                       ,[计划价]
                        ,[年])
                            VALUES ( '{0}',{1},'{2}')",PlanPrice[0],PlanPrice[2],PlanPrice[1]);
                   db.ExecuteCommand(sql);
               }else if(q.Count()==1)
                {
                    sql= string.Format(@"UPDATE  [TABC_JS_Meterial_PlanPrice]
                     SET  [计划价] = {2} 
                      WHERE [材料ID]='{0}' and [年]='{1}' ",PlanPrice[0],PlanPrice[1],PlanPrice[2]);
                    db.ExecuteCommand(sql);
               }


                          

            }


            return true;
        }




        /// <summary>
        /// 同步领用人信息
        /// 后台就是取oracle数据库中的用户名
        /// </summary>
        /// <returns></returns>
        public bool Username_Sync()
        {
            List<string> Users = GetUsername();
            List<TABC_DM_CostCenter_Ref_Username> updaterows = new List<TABC_DM_CostCenter_Ref_Username>();
            var delusers = from a in db.TABC_DM_CostCenter_Ref_Username where
                               a.作业中心ID == null 
                          select a;

            db.TABC_DM_CostCenter_Ref_Username.DeleteAllOnSubmit(delusers);
            db.SubmitChanges();
            var existusers = (from a in db.TABC_DM_CostCenter_Ref_Username
                            select new
                            {
                                a.领用人
                            }).ToArray();
                          
            bool isadd=true;
            int i;
            foreach (string User in Users)
            {
                isadd = true;
                for (i = 0; i < existusers.Count();i++ )
                {
                    if ( User==existusers[i].领用人)
                    {
                        isadd = false;
                    }
                }
                if (isadd)
                {
                    TABC_DM_CostCenter_Ref_Username updaterow = new TABC_DM_CostCenter_Ref_Username();
                    updaterow.领用人 = User;
                    updaterows.Add(updaterow);
 
                }
            }
            foreach (TABC_DM_CostCenter_Ref_Username urow in updaterows)
            {
                db.TABC_DM_CostCenter_Ref_Username.InsertOnSubmit(urow);
            }
            db.SubmitChanges();
            return true;
        }


        public bool MeterialBM_Sync()
        {
            List<string[]> MeterialBMs = Get_MeterialBM();
           List<TABC_JS_Meterial> updaterows = new List<TABC_JS_Meterial>();
       
            // db.SubmitChanges; 
            db.ExecuteCommand("delete from TABC_JS_Meterial");
           // db.SubmitChanges();
            //var existBMs = (from a in db.TABC_JS_Meterial
            //                       select  a.材料ID
            //                   ).ToArray();
             

            //bool isadd = true;
           
 
            foreach (string[] MeterialBM in MeterialBMs)
            {
                //isadd = true; 
                ////for (i = 0; i < existBMs.Count(); i++)
                ////{
                ////    if (MeterialBM[0] == existBMs[i].材料ID)
                ////    {
                ////        isadd = false;
                ////    }
                ////}
                //if (isadd)
                //{
                    TABC_JS_Meterial updaterow = new TABC_JS_Meterial();
                    
                    updaterow.公司ID="hisun";                    
                    updaterow.材料ID = MeterialBM[0];
                    updaterow.材料名称=MeterialBM[1];
                    updaterow.计量单位=MeterialBM[2];
                    updaterow.规格型号 = MeterialBM[3];
                    updaterows.Add(updaterow);
            }
            foreach (TABC_JS_Meterial urow in updaterows)
            {
                db.TABC_JS_Meterial.InsertOnSubmit(urow);
            }
                  

            db.SubmitChanges();
            return true;
        }

        /// <summary>
        /// 取所有材料编码
        /// </summary>
        /// <returns></returns>
        private List<string[]> Get_PlanPrice()
        {
            List<string[]> PlanPrice = new List<string[]>();

            string sql = @"
select cl.vcbm,pl.nyear,max(pl.nplan_price) 
from tca_planpriceofmaterial pl,tbclb cl  
where pl.nyear=to_char(:KJQJ  ,'YYYY') 
and pl.nmaterial_id=cl.nhxh  and pl.nplan_price is not null
group  by cl.vcbm ,pl.nyear ";
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add("KJQJ", OracleType.DateTime).Value = getkjqj();
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string[] row = new string[3];
                row[0] = reader[0].ToString();
                row[1] = reader[1].ToString();
                row[2] = reader[2].ToString();
                PlanPrice.Add(row);
            }
            return PlanPrice;


        }

        /// <summary>
        /// 取所有材料编码
        /// </summary>
        /// <returns></returns>
        private List<string[]> Get_MeterialBM()
        {
            List<string[]> MeterialBM = new List<string[]>();
           
            //string sql = @"select vcbm as 材料编码 , max(vcmc) as 材料名称,max(vcjldw) as 计量单位,max(vcgg) as 规格型号 from  tbclb  group by  vcbm    order by vcbm";
            //2010年9月28日更新
            string sql = @"  select 材料编码,max(材料名称),max(计量单位),max(规格型号) from  (select vcbm as 材料编码 , vcmc  as 材料名称,vcjldw  as 计量单位,vcgg as 规格型号 from tbclb 
                            union all  
                             select vcbm as 材料编码 ,vctym as 材料名称,vcjldw as 计量单位, vcgg as 规格型号 from tbcpb  )  group by 材料编码 order by 材料编码  ";
            
            
            OracleCommand cmd = new OracleCommand(sql, conn);
             OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                 string[] row = new string[4];
                row[0] =  reader[0].ToString();
                row[1] =  reader[1].ToString();
                row[2] =  reader[2].ToString();
                row[3] =  reader[3].ToString();
                MeterialBM.Add(row);
            }
            return MeterialBM;


        }

        /// <summary>
        /// 取所有用户名
        /// </summary>
        /// <returns></returns>
        private List<string> GetUsername()
        {
            List<string> Users=new List<string>();
            string sql = @"select distinct vcout_username from tst_mstorageout_m m where  m.nbill_type in('20','23')　
               and m.ncompany_id =  4  and  vcout_username is not null
              and  m.drecord_date=:KJQJ  and ( ninner_account_id is not null or vcout_username like '%协调' ) ";
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.Parameters.Add("KJQJ", OracleType.DateTime).Value = getkjqj();
            OracleDataReader reader=cmd.ExecuteReader();
            while (reader.Read())
            {
              Users.Add(reader[0].ToString());
            }
            return Users;
             

        }
        /// <summary>
        /// 取oracle数据库中的成本帐户
        /// </summary>
        private DataTable GetAccount_Info()
        {
            DataTable dt = new DataTable();
            string sql = @"select  t.vcaccount_no  as 产品账户ID
,t.vcaccount_name  as 产品账户名称
,cp.vcbm as 产品ID
,cp.vctym as 产品名称
,cp.vctympy as 产品辅码
,cp.vczjm  as 产品助记码
, b.vczzmc  as 车间
, cp.vcjldw as 计量单位
,cp.vcgg as 规格型号
,t.nstop_mark  as 停用  from tge_inner_account t,tbzzb b,tbcpb cp  where t.vcaccount_no like '2%' and t.ndept_id is not null 
and t.ndept_id=b.nhxh
and t.nprod_id=cp.nhxh(+)   ";

            try
            {
                OracleCommand cmd = new OracleCommand(sql, conn);
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(dt);
            }
            catch
            {
                throw new Exception("读取财务Oracle数据库失败");
            }
            return dt;

        }

        private DateTime getkjqj()
        {

            //DateTime q = (DateTime)(from a in db.TABC_JS_AccountPeriods
            //                        where a.会计期间ID == kjqj
            //                        select new
            //                        {
            //                            a.成本开始日期
            //                        }).Single().成本开始日期;
            //2009年7月28日杨莹 修改。
            //海正现有的系统中，会计期间是用2009-07-01这种形式来表示的。
            //在成本系统中，成本开始日期不是当月1日。因此做以下修改。

            DateTime q = DateTime.Parse(kjqj.Substring(0,4)+"-"+kjqj.Substring(4,2)+"-01");
            return q;
        }





        private DataTable getcailiao()
        {
            DataTable dt = new DataTable();
            string sql = @"select m.NID ,
ina2.vcaccount_no 产品ID
, ina2.vcaccount_name 产品名称
,m.vcout_username 领用人,
m.dout_date 出库日期,
zb.vczzmc 部门,cl.vcbm 材料编码,cl.vcmc 材料名称,cl.vcjldw 计量单位,nvl(dd.namount,0) 数量,nvl(dd.nmoney,0) 金额,
m.vcbill_no 领料单号
--dd.nmoney/dd.namount 单价
 from tst_mstorageout_m m,
      tst_mstorageout_d d ,
      tma_materialinout_m mm ,
      tma_materialinout_d dd,
      tbclb cl,
      tbzzb zb,
      vw_inner_account ina2

where  m.nid=d.tst_nid 
       and mm.nid=dd.nid
       and mm.nstoreout_master_id=m.nid
       and mm.nstoreout_detail_id=d.nid
       and d.nmaterial_id=cl.nhxh 
       and m.ndept_id=zb.nhxh
       and m.ninner_account_id= ina2.nhxh(+)
       and (m.nbill_type='20' or m.nbill_type='23')   --20 领用 23退回
       --and m.nstorage_id='61'   --主要原材料仓库
       and (m.ninner_account_id is not null  or m.vcout_username like '%协调' )--生产帐户不为空（生产领料）
       and m.drecord_date = :KJQJ  --to_date('2009-01-01','yyyy-mm-dd')  
     

";
#region  注释掉的原材料集成代码 
//            string sql = @"select
//NID , 
//vcout_username as 领用人,
// 　会计科目, 
//部门,
// 产品ID,
// 产品名称,
//Dout_date as 出库日期,
//vcBM as 材料编码,
//PMGG as 材料名称,
//VCJLDW as 计量单位,
// 单价*namount  as 金额,
//namount as 数量,
//单价,　
//0 as 产品领料
// from
//       (select   d.namount,decode(mt.nprice,0,d.nprice,mt.nprice) as 单价 ,m.vcout_username,decode(m.ninner_account_id,null,'',ina1.vcaccount_name)  部门 ,ina2.vcaccount_no as  产品ID,ina2.vcaccount_name  as 产品名称  ,m.nid,m.dout_date,d.nmoney as nmoney,d.nprice,dept.vcdeptname as 会计科目,clb.vcbm,clb.vcmc||clb.vcgg as pmgg,clb.vcjldw
//               from tst_mstorageout_m m,tst_mstorageout_d d,vw_dept dept,tbclb clb,vw_inner_account_dept ina1,vw_inner_account ina2 
//             ,  tma_materialaccount  mt
//             where   m.nid = d.tst_nid 
//               and m.ndept_id = dept.ndeptid(+)          
//               and d.nmaterial_id = clb.nhxh 
//               and m.ndept_id = ina1.ndept_id(+)   
//               and m.ninner_account_id = ina2.nhxh(+)     
//               and d.nfin_confirm_mark = 1 
//               and m.ncompany_id =  4  
//               and mt.ngoods_id(+)=d.nmaterial_id
//                 and mt.narea_id = '-1' and mt.nyear=to_char(add_months( :KJQJ  ,0),'YYYY')  and mt.nmonth= to_char(add_months(:KJQJ  ,0),'mm') 
//             --  and m.ninner_account_id is not null
//            --   and m.nstorage_id =  61  
//           and m.nbill_type in('20','23')
//               and m.drecord_date = :KJQJ   ) b where  产品ID is not null or  vcout_username  like '%协调' 
//union all
// 
//select  m.nid,
//m.vcout_username as 领用人,
//vczzmc as 会计科目,
//vczzmc as 部门,
//ina2.vcaccount_no as 产品ID,
//ina2.vcaccount_name as 产品名称,
//m.drecord_date as 出库时间,
//b.vcbm as 材料编码,
//b.vctym as 材料名称,
//b.vcjldw as 计量单位,
//mt.npre_price*d.namount as 金额,
//d.namount as 数量,
//mt.npre_price as 单价,
//1 as 产品领料
// from  tst_mstorageoutp_m m,tst_mstorageoutp_d d,tbcpb b,tbzzb tb, vw_inner_account ina2,
// tsa_prodaccount  mt
//where tb.nhxh=m.ndept_id 
// and m.ninner_account_id(+)= ina2.nhxh  
//and mt.ngoods_id=d.nmaterial_id
//and m.nid=d.tst_nid  
//and m.ngoods_type='1' 
//and m.drecord_date=:KJQJ  
//and  mt.nyear=to_char(:KJQJ   ,'YYYY')  and mt.nmonth= to_char(:KJQJ   ,'mm') 
// and d.nmaterial_id=b.nhxh
//and m.ninner_account_id is not null
//
//";
////            string sql = @" select
////NID , 
////vcout_username as 领用人,
//// 　会计科目, 
////部门,
//// 产品ID,
//// 产品名称,
//Dout_date as 出库日期,
//vcBM as 材料编码,
//PMGG as 材料名称,
//VCJLDW as 计量单位,
// 单价*namount  as 金额,
//namount as 数量,
//单价,　
//0 as 产品领料,
// from
//       (select   d.namount,mt.npre_price as 单价 ,m.vcout_username,decode(m.ninner_account_id,null,'',ina1.vcaccount_name)  部门 ,ina2.vcaccount_no as  产品ID,ina2.vcaccount_name  as 产品名称  ,m.nid,m.dout_date,d.nmoney as nmoney,d.nprice,dept.vcdeptname as 会计科目,clb.vcbm,clb.vcmc||clb.vcgg as pmgg,clb.vcjldw
//               from tst_mstorageout_m m,tst_mstorageout_d d,vw_dept dept,tbclb clb,vw_inner_account_dept ina1,vw_inner_account ina2 
//             ,  tma_materialaccount  mt
//             where   m.nid = d.tst_nid 
//               and m.ndept_id = dept.ndeptid(+)          
//               and d.nmaterial_id = clb.nhxh 
//               and m.ndept_id = ina1.ndept_id(+)   
//               and m.ninner_account_id = ina2.nhxh(+)     
//               and d.nfin_confirm_mark = 1 
//               and m.ncompany_id =  4  
//               and mt.ngoods_id(+)=d.nmaterial_id
//                 and mt.narea_id = '-1' and mt.nyear=to_char(add_months( :KJQJ ,-1),'YYYY')  and mt.nmonth= to_char(add_months( :KJQJ ,-1),'mm') 
//             --  and m.ninner_account_id is not null
//            --   and m.nstorage_id =  61  
//           and m.nbill_type in('20','23')
//               and m.drecord_date = :KJQJ  ) b where  产品ID is not null or  vcout_username  like '%协调' 
//union all
// 
//select  m.nid,
//m.vcout_username as 领用人,
//vczzmc as 会计科目,
//vczzmc as 部门,
//ina2.vcaccount_no as 产品ID,
//ina2.vcaccount_name as 产品名称,
//m.drecord_date as 出库时间,
//b.vcbm as 材料编码,
//b.vctym as 材料名称,
//b.vcjldw as 计量单位,
//mt.npre_price*d.namount as 金额,
//d.namount as 数量,
//mt.npre_price as 单价,
//1 as 产品领料
// from  tst_mstorageoutp_m m,tst_mstorageoutp_d d,tbcpb b,tbzzb tb, vw_inner_account ina2,
// tsa_prodaccount  mt
//where tb.nhxh=m.ndept_id 
// and m.ninner_account_id(+)= ina2.nhxh  
//and mt.ngoods_id=d.nmaterial_id
//and m.nid=d.tst_nid  
//and m.ngoods_type='1' 
//and m.drecord_date=:KJQJ 
//and  mt.nyear=to_char(:KJQJ  ,'YYYY')  and mt.nmonth= to_char(:KJQJ  ,'mm') 
// and d.nmaterial_id=b.nhxh
//and m.ninner_account_id is not null
//
//
//";     
            /*
             * 
             * string sql = @"select
NID  , 
vcout_username as 领用人,
b.vcaccount_name as　会计科目, 
b.vcaccount_deptname 部门,
b.naccount_prodid as 产品ID,
naccount_prodname as 产品名称,
Dout_date as 出库日期,
vcBM as 材料编码,
PMGG as 材料名称,
VCJLDW as 计量单位,
Nmoney as 金额,
namount as 数量,
nprice as 单价
　
 from
       (select m.vcout_username,m.nbill_type,decode(m.ninner_account_id,null,-1,ina1.nhxh)naccount_deptid,decode(m.ninner_account_id,null,'',ina1.vcaccount_name) vcaccount_deptname ,decode(m.ninner_account_id,null,'',ina2.vcaccount_no) naccount_prodid,ina2.vcaccount_name  as naccount_prodname,m.nid,m.dout_date,m.vcbill_no,m.nstorage_id,m.ndept_id,m.nfin_confirm_mark,d.nid as ndetail_id,d.narea_id,d.nmaterial_id,d.namount,d.nfact_number,d.nmoney as nmoney,d.nprice,vs.vcstoragename,dept.vcdeptname as vcaccount_name,dept.vcdeptno as vcaccount_no,clb.vcbm,clb.vcmc||clb.vcgg as pmgg,clb.vcjldw,clb.nxsdwz,'' as nbzh,'' as nbzh_prod,'' as czr,0 as selectbj 
               from tst_mstorageout_m m,tst_mstorageout_d d,vw_dept dept,tbclb clb,vw_storageandarea vs,vw_inner_account_dept ina1,vw_inner_account ina2 
               where  m.nid = d.tst_nid 
               and m.ndept_id = dept.ndeptid(+) 
               and d.narea_id = vs.nstorageid 
               and d.nmaterial_id = clb.nhxh 
               and m.ndept_id = ina1.ndept_id(+)   
               and m.ninner_account_id = ina2.nhxh(+)     
               and d.nfin_confirm_mark = 1 
               and m.ncompany_id =  4  
               and m.nstorage_id =  61  
               and m.drecord_date = :KJQJ) b where Nbill_type='20'　　"*****/
#endregion
            try
            {
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.Parameters.Add("KJQJ", OracleType.DateTime).Value = getkjqj();
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(dt);
            }
            catch
            {
                throw new Exception("读取财务Oracle数据库失败");
            }
            return dt;


        }

    }
}
